import pymysql
from todo import Todo
class TodoManagerWithMysql:
    @staticmethod
    def get_connection():
        return pymysql.Connection(host='192.168.80.130',database='test0825',user='root',password='root@123ABC',autocommit=True)
    def create(self,title:str):
        with self.get_connection() as c:
            # print(c)
            s = c.cursor()
            # print(s)
            s.execute('insert into todos (title) value(%s)',(title,))
            s.execute('select last_insert_id()')
            (id,) =s.fetchone()
            return Todo(title,id=id)
            # print(res)
            # data=s.fetchall()
            # print(data)
    # def init_db(self):
    #     with self.get_connection() as c:
    #         create_table = '''
    #         create table if not exists `todos`(
    #         `id` int primary key NOT NULL AUTO_INCREMENT,
    #         `title` varchar(50) DEFAULT '',
    #         `completed` int DEFAULT 0,
    #         )'''
    #         s = c.cursor()
    #         s.execute(create_table)
    def init_db(self):
        with self.get_connection() as c:
            s = c.cursor()
            s.execute('''
            create table if not exists todos(
            id int primary key not null auto_increment,
            title varchar(50) not null,
            completed int default 0
            );
            ''')


    def convent_row_to_object(self,i):
        return Todo(i[1],id=i[0],completed=True if i[2] == 1 else False)
    def get_list(self):
        with self.get_connection() as c:
            s = c.cursor()
            s.execute('select * from todos')
            data = s.fetchall()
            list_todos = []
            for i in data:
                list_todos.append(self.convent_row_to_object(i))
            return list_todos

    def get_id(self,id):
        with self.get_connection() as c:
            s = c.cursor()
            s.execute('select * from todos')
            for i in self.get_list():
                if i.id == id:
                    return i
    def mark_completed(self,id):
            todo = self.get_id(id)
            if todo is not None:
                todo.completed = True
                with self.get_connection() as c:
                    s = c.cursor()
                    s.execute('UPDATE todos SET completed = 1 WHERE id =(%s)',(id,))
                    return todo
    def del_todos(self, id):
        with self.get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute('select * from todos where id=%s', (id,))
            s=self.get_id(id)
            if s is not None:
                cursor.execute('delete from todos where id=%s', (id,))
                return s



